<?php
require_once 'config.php';

header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    // 连接MySQL（不指定数据库）
    $conn = new mysqli(
        DB_CONFIG['host'],
        DB_CONFIG['username'],
        DB_CONFIG['password']
    );
    
    if ($conn->connect_error) {
        throw new Exception("连接MySQL失败: " . $conn->connect_error);
    }

    // 设置字符集
    if (!$conn->set_charset('utf8mb4')) {
        throw new Exception("设置字符集失败: " . $conn->error);
    }

    // 创建数据库
    $dbName = DB_CONFIG['database'];
    if (!$conn->query("CREATE DATABASE IF NOT EXISTS `$dbName` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")) {
        throw new Exception("创建数据库失败: " . $conn->error);
    }

    // 选择数据库
    if (!$conn->select_db($dbName)) {
        throw new Exception("选择数据库失败: " . $conn->error);
    }

    // 删除已存在的表和事件
    $conn->query("DROP TABLE IF EXISTS `notifications`");
    $conn->query("DROP TABLE IF EXISTS `message_templates`");
    $conn->query("DROP TABLE IF EXISTS `configs`");
    $conn->query("DROP EVENT IF EXISTS `clean_old_notifications`");

    // 创建配置表
    $sql = "CREATE TABLE `configs` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `phone` varchar(20) NOT NULL COMMENT '车主电话',
        `admin_password` varchar(255) NOT NULL DEFAULT '123456' COMMENT '管理密码',
        `cooldown` int(11) NOT NULL DEFAULT 60000 COMMENT '通知冷却时间(毫秒)',
        `default_message` text NOT NULL COMMENT '默认通知消息',
        `app_token` varchar(100) NOT NULL COMMENT 'WxPusher AppToken',
        `uid` varchar(100) NOT NULL COMMENT 'WxPusher 用户ID',
        `api_url` varchar(255) NOT NULL COMMENT 'WxPusher API地址',
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_phone` (`phone`),
        KEY `idx_updated` (`updated_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表'";

    if (!$conn->query($sql)) {
        throw new Exception("创建配置表失败: " . $conn->error);
    }

    // 创建消息模板表
    $sql = "CREATE TABLE `message_templates` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `content` text NOT NULL COMMENT '模板内容',
        `sort_order` int(11) NOT NULL DEFAULT 0 COMMENT '排序顺序',
        `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
        `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_active_sort` (`is_active`, `sort_order`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消息模板表'";

    if (!$conn->query($sql)) {
        throw new Exception("创建消息模板表失败: " . $conn->error);
    }

    // 创建通知记录表
    $sql = "CREATE TABLE `notifications` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `message` text NOT NULL COMMENT '通知内容',
        `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '发送状态：1成功，0失败',
        `error_msg` text COMMENT '错误信息',
        `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `idx_timestamp` (`timestamp`),
        KEY `idx_status` (`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通知记录表'";

    if (!$conn->query($sql)) {
        throw new Exception("创建通知记录表失败: " . $conn->error);
    }

    // 插入默认配置
    $sql = "INSERT INTO `configs` SET
        phone = '13800138000',
        admin_password = '123456',
        cooldown = 60000,
        default_message = '您好，您的爱车需要移动，请尽快处理，谢谢配合。',
        app_token = 'AT_xxx',
        uid = 'UID_xxx',
        api_url = 'https://wxpusher.zjiecode.com/api/send/message'";

    if (!$conn->query($sql)) {
        throw new Exception("插入默认配置失败: " . $conn->error);
    }

    // 插入默认消息模板
    $templates = [
        ['您好，您的爱车挡住了出口，请尽快移动车辆。', 1],
        ['紧急情况，需要您立即移车，谢谢配合！', 2],
        ['您的车辆占用了消防通道，请立即处理，谢谢！', 3],
        ['您的车辆影响施工，请及时挪车，感谢理解。', 4],
        ['您的车辆影响其他车辆进出，请尽快移动。', 5]
    ];

    foreach ($templates as $template) {
        $content = $conn->real_escape_string($template[0]);
        $order = $template[1];
        $sql = "INSERT INTO `message_templates` (content, sort_order) VALUES ('$content', $order)";
        if (!$conn->query($sql)) {
            throw new Exception("插入消息模板失败: " . $conn->error);
        }
    }

    // 创建自动清理事件
    $conn->query("SET GLOBAL event_scheduler = ON");
    
    $sql = "CREATE EVENT IF NOT EXISTS `clean_old_notifications`
        ON SCHEDULE EVERY 1 DAY
        STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
        DO
        BEGIN
            DELETE FROM notifications WHERE timestamp < DATE_SUB(NOW(), INTERVAL 30 DAY);
        END";

    if (!$conn->query($sql)) {
        throw new Exception("创建清理事件失败: " . $conn->error);
    }

    // 验证数据库结构
    $requiredTables = ['configs', 'message_templates', 'notifications'];
    foreach ($requiredTables as $table) {
        $result = $conn->query("SHOW TABLES LIKE '$table'");
        if ($result->num_rows === 0) {
            throw new Exception("表 '$table' 创建失败");
        }
        
        // 验证表中是否有数据
        if ($table !== 'notifications') {
            $count = $conn->query("SELECT COUNT(*) as count FROM $table")->fetch_assoc()['count'];
            if ($count === 0) {
                throw new Exception("表 '$table' 初始数据插入失败");
            }
        }
    }

    $conn->close();
    
    echo json_encode([
        'success' => true,
        'message' => '数据库初始化成功！请刷新页面使用系统。'
    ]);

} catch (Exception $e) {
    if (isset($conn)) {
        $conn->close();
    }
    
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}